﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Management;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.Data.Sql;
using System.Runtime.InteropServices;
using System.Management.Instrumentation;
using System.Threading;
//using Microsoft.SqlServer.Management.Smo;
namespace MyTest
{
    class XMLWriter
    {
        ///member vars
        ///
        private string mServerString;
        private string mInstanceString;
        private string mConnString;
        private string mXmlPath;
        private SqlConnection mConnection;
        private XmlDocument xmlDoc;
        private XmlElement mInstance;
        ///

        public int GetPlatformBit()
        {
            try
            {
                string query_xp_platform = "xp_msver 'Platform'";
                SqlCommand cmd0 = new SqlCommand(query_xp_platform, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_xp_platform;
                using (var dr_xp_platform = cmd0.ExecuteReader())
                {
                    string result;
                    string value;
                    while (dr_xp_platform.Read())
                    {
                        result = dr_xp_platform[3].ToString();
                        value = result.Substring(result.ToUpper().IndexOf('X') + 1, 2);
                        Console.WriteLine(value);
                        dr_xp_platform.Close();
                        if (value == "86") return 32;
                        else if (value == "64") return 64;
                        else return 0;
                    }
                }
               
            }
            catch (Exception ex)
            {
                Console.WriteLine("no platform info found");
                return 0;
            }
            finally
            {

            }
            return 0;

        }
        XMLWriter(string serverName, string instanceName)
        {
            Console.WriteLine("Connect begins");
            if (instanceName.Equals("MSSQLServer".ToUpper()))
            {
                mConnString = "Data Source=" + serverName + ";Initial Catalog=Master;Integrated Security=true;Connect Timeout=30;";
            }
            else
            {
                mConnString = "Data Source=" + serverName + "\\" + instanceName + ";Initial Catalog=Master;Integrated Security=true;Connect Timeout=30;";
            }
            mServerString = serverName;
            mInstanceString = instanceName;
            mXmlPath = "D:\\xx.xml";
            try
            {
                //make the connection
                mConnection = new SqlConnection(mConnString);
                mConnection.Open();
                Console.WriteLine("connection successful");
                //initialize the XML Doc
                xmlDoc = new XmlDocument();
                XmlElement root = xmlDoc.CreateElement("ComputerName");//server name
                root.SetAttribute("name", serverName+GetPlatformBit());
                xmlDoc.AppendChild(root);
                mInstance = xmlDoc.CreateElement("InstanceName");//instance name
                mInstance.SetAttribute("name", instanceName);
                root.AppendChild(mInstance);
            }
            catch (System.Exception ex)
            {
                mConnString = null;
                Console.WriteLine("connection failed!Please Check the Server or Instance name");
                System.Environment.Exit(0);
            }

        }
        XMLWriter(string serverName, string instanceName, string xmlPath):this(serverName, instanceName)
        {
            mXmlPath = xmlPath;
        }
        ~XMLWriter()
        {
            try
            {
                mConnection.Close();
            }
            catch (System.Exception ex)
            {
                Console.WriteLine("connection failed to destroy!");
                System.Environment.Exit(0);
            }
            finally
            {
                mConnection = null;
            }
        }
        public void Close()
        {
            try
            {
                mConnection.Close();
                Console.WriteLine("Connection closed");

            }
            catch (System.Exception ex)
            {
                mConnection = null;
                Console.WriteLine("connection failed to close!");
                System.Environment.Exit(0);
            }
        }
        public void SystemCheck()//Check the System overview status
        {
            Console.WriteLine("SystemCheck----");
            //string result = null;
            XmlElement system = xmlDoc.CreateElement("System");
            mInstance.AppendChild(system);
            //whether it's a DC
            Console.WriteLine("    DCCheck----");
            UInt32 uDomainRole=0;
            string query_dc = "SELECT DomainRole FROM Win32_ComputerSystem";
            ManagementObjectSearcher searcher;
            try
            {
                SelectQuery selectQuery = new SelectQuery(query_dc);
                searcher = new ManagementObjectSearcher(selectQuery);
                foreach (ManagementObject mo in searcher.Get())
                {
                    uDomainRole =Convert.ToUInt32(mo["DomainRole"]);
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine("wmi call failed!");
                return;
            }
            XmlElement domaineControl = xmlDoc.CreateElement("DC");
            domaineControl.InnerText = Convert.ToString(uDomainRole>3?1:0);
            system.AppendChild(domaineControl);

            //PortQry
            Console.WriteLine("    ProtocolsCheck----");
            XmlElement PortQry = xmlDoc.CreateElement("Protocols");
            system.AppendChild(PortQry);
            System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo();
            info.FileName = "PortQry.exe";
            info.Arguments = "-n " + mServerString + " -p UDP -e 1434 -v";
            info.WorkingDirectory = System.IO.Path.GetFullPath(".\\");
            info.UseShellExecute = false;
            info.RedirectStandardError = false;
            info.RedirectStandardInput = false;
            info.RedirectStandardOutput = true;
            System.Diagnostics.Process pro;
            string netMessage;
            try
            {
                pro = System.Diagnostics.Process.Start(info);
                netMessage = pro.StandardOutput.ReadToEnd();
                //Console.WriteLine(netMessage);
            }
            catch (System.ComponentModel.Win32Exception ex)
            {
                Console.WriteLine("can't find PortQry.exe。\n\r{0}", ex.ToString());
                return;
            }
            pro.WaitForExit(500);
            if (pro.HasExited == false)
            {
                pro.Kill();
            }
            //analyze the netMessage(if there are no info we want,display it)
            string string_protocal;
            try
            {
                int substring_start = netMessage.IndexOf(mInstanceString.ToUpper(), 0);
                string substring_estimate = netMessage.Substring(substring_start, 70);
                int substring_end = substring_estimate.IndexOf("tcp") + 8;
                string_protocal = substring_estimate.Substring(0, substring_end);
                string substring_isClustered = string_protocal.Substring(string_protocal.IndexOf("IsClustered") + 12, 2);
                string substring_Version = string_protocal.Substring(string_protocal.IndexOf("Version") + 8, string_protocal.IndexOf("tcp") - string_protocal.IndexOf("Version") - 9);
                string substring_tcp = string_protocal.Substring(string_protocal.IndexOf("tcp") + 4, string_protocal.Length - string_protocal.IndexOf("tcp") - 4);
                XmlElement TCP = xmlDoc.CreateElement("TCP");
                TCP.InnerText = substring_tcp;
                PortQry.AppendChild(TCP);
            }

            catch
            {
                Console.WriteLine("can't get the Protocal information! you may not turn on SQL SERVER Browser or firewall issue");
                return;
            }
            //SQL Server Service Pack check
            Console.WriteLine("    ServicePackCheck----");
            try
            {
            string query_sqlserver_servicepack = "xp_msver 'ProductVersion'";
            SqlCommand cmd0 = new SqlCommand(query_sqlserver_servicepack, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_sqlserver_servicepack;
            using (var dr_sqlserver_servicepack = cmd0.ExecuteReader())
            {


                while (dr_sqlserver_servicepack.Read())
                {
                    XmlElement ServicePackVersion = xmlDoc.CreateElement("SQLSP");
                    ServicePackVersion.InnerText = dr_sqlserver_servicepack[3].ToString();
                    system.AppendChild(ServicePackVersion);
                }
                dr_sqlserver_servicepack.Close();
            }
             }
            catch (Exception ex)
            {
                Console.WriteLine("get productversion info error!");
                return;
            }
            //return result;
        }

        public void SystemMaintainCheck()//check the system maintain 
        {
            Console.WriteLine("SystemMaintainCheck----");
            XmlElement systemMaintain = xmlDoc.CreateElement("SystemMaint");
            mInstance.AppendChild(systemMaintain);
            //create a sp to return the lastCheckDBTime
            Console.WriteLine("    lastCheckDBTime----");
            XmlElement lastGoodCheck = xmlDoc.CreateElement("LastKnownGoodCheckDB");
            systemMaintain.AppendChild(lastGoodCheck);
            string query_use_master = "use master";
            SqlCommand cmd0 = new SqlCommand(query_use_master, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_use_master;
            cmd0.ExecuteReader().Close();

            string query_create_sp = "Create proc CheckDBClean "
            + "@myDBName nvarchar(2000) "
            + "as "
            + "DECLARE @dbinfo TABLE(ParentObject varchar(255),Object varchar(255),Field varchar(255),Value varchar(255)); "
            + "Declare @lastCheckDBTime datetime "
            + "insert INTO @dbinfo EXEC ('DBCC DBINFO ('+@mydbname+') WITH TABLERESULTS '); "
            + "select @lastcheckdbtime= value from @dbinfo where  ParentObject = 'DBINFO STRUCTURE:' AND Field = 'dbi_dbccLastKnownGood' "
            + "select convert(varchar, @lastCheckDBTime, 121) ";
            //Console.WriteLine(query_create_sp);
            cmd0 = new SqlCommand(query_create_sp, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_create_sp;
            try
            {
                cmd0.ExecuteReader().Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("create procedure failed!");
                DeleteTempProc("CheckDBClean");
                return;
            }
            try
            {
                //list all the dataBase
                string query_list_databases = "select name from sys.databases";
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(query_list_databases, mConnection);
                sda.Fill(ds, "AllDatabases");
                DataTable dt = ds.Tables["AllDatabases"];
                foreach (DataRow dataRow in dt.Rows)
                {
                    //Console.WriteLine(dataRow["name"]);
                    string query_runSP = "CheckDBClean " + " '[" + dataRow["name"] + "]' ";
                    SqlCommand cmd = new SqlCommand(query_runSP, mConnection);
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = query_runSP;
                    using (var dr_runSP = cmd.ExecuteReader())
                    {
                        while (dr_runSP.Read())
                        {
                            XmlElement lastgood = xmlDoc.CreateElement("DB");
                            lastgood.SetAttribute("name", dataRow["name"].ToString());
                            lastgood.SetAttribute("dbccLastKnownGood", dr_runSP[0].ToString());
                            lastGoodCheck.AppendChild(lastgood);
                        }
                        dr_runSP.Close();
                    }
                }

                ///drop the sp
                string query_dropSP = "drop proc CheckDBClean";
                cmd0 = new SqlCommand(query_dropSP, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_dropSP;
                cmd0.ExecuteReader().Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("run proc CheckDBClean encounter failure,will call the drop proc statement in the finally\r\n  " + ex.ToString());
                return;
            }
            finally
            {
                DeleteTempProc("CheckDBClean");
            }

            //Check Private Key backup
            Console.WriteLine("    PrivateKeyBackup ----");
            XmlElement privKeyBackUp = xmlDoc.CreateElement("PrivateKeyBackup");
            systemMaintain.AppendChild(privKeyBackUp);
            
                string query_private_key_backup = "SELECT pvt_key_last_backup_date, "
                + "Db_name(dek.database_id) AS encrypteddatabase ,c.name AS Certificate_Name "
                + "FROM   sys.certificates c "
                + "INNER JOIN sys.dm_database_encryption_keys dek "
                + "ON c.thumbprint = dek.encryptor_thumbprint ";
                cmd0 = new SqlCommand(query_private_key_backup, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_private_key_backup;

               
                    try
                    {
                        using (var dr_private_key_bakup = cmd0.ExecuteReader())
                        {

                            while (dr_private_key_bakup.Read())
                            {
                                XmlElement KeyBackup = xmlDoc.CreateElement("KeyBackup");
                                KeyBackup.SetAttribute("pvt_key_last_backup_date", dr_private_key_bakup[0].ToString());
                                KeyBackup.SetAttribute("encrypteddatabase", dr_private_key_bakup[1].ToString());
                                KeyBackup.SetAttribute("Certificate_Name", dr_private_key_bakup[2].ToString());
                                privKeyBackUp.AppendChild(KeyBackup);
                            }
                           // dr_private_key_bakup.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("The dm_database_encryption_keys DMV doesn't exist in SQL Server 2005");
                        return;
                    }



                    //Check Database without backup
                    Console.WriteLine("    DBWithoutBackup----");
              try
                    {

                    
            XmlElement NoBackup = xmlDoc.CreateElement("DBWithoutBackup");
            systemMaintain.AppendChild(NoBackup);
            string query_nobackup_databases = "select a.name from sys.databases a left outer join msdb.dbo.backupset b "
             + "on a.name = b.database_name where DATEDIFF(DAYOFYEAR,getdate(),b.backup_finish_date)>14 "
             + "union all "
             + "select a.name from sys.databases a left outer join msdb.dbo.backupset b "
             + "on a.name = b.database_name where backup_start_date is NULL ";
            cmd0 = new SqlCommand(query_nobackup_databases, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_nobackup_databases;
            using (var dr_nobackup_databases = cmd0.ExecuteReader())
            {


                while (dr_nobackup_databases.Read())
                {
                    XmlElement nobak = xmlDoc.CreateElement("DB");
                    nobak.SetAttribute("Name", dr_nobackup_databases[0].ToString());
                    NoBackup.AppendChild(nobak);
                }
                dr_nobackup_databases.Close();
            }
                        }
            catch(Exception ex)
                    {
                        Console.WriteLine("get DBWithoutBackup error");
                        return;
                    }


            //Check database Log backup: 
            Console.WriteLine("    DBWithoutLogBackup----");
            XmlElement NoLogBackup = xmlDoc.CreateElement("DBWithoutLogBackup");
            systemMaintain.AppendChild(NoLogBackup);
            try
            {

                string query_nologbackup_databases = "select name from sys.databases where name not in( "
                 + "select database_name from msdb.dbo.backupset  where type = 'L' )";
                cmd0 = new SqlCommand(query_nologbackup_databases, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_nologbackup_databases;
                using (var dr_nologbackup_databases = cmd0.ExecuteReader())
                {


                    while (dr_nologbackup_databases.Read())
                    {
                        XmlElement nologbak = xmlDoc.CreateElement("DB");
                        nologbak.SetAttribute("Name", dr_nologbackup_databases[0].ToString());
                        NoLogBackup.AppendChild(nologbak);
                    }
                    dr_nologbackup_databases.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("get nologbackup errpr");
                return;
            }

            //Check the tempdb data files and CPU number
            Console.WriteLine("    NumOfTempDB----");
            string query_tempdb_cpu = "declare @MyCPU int "
             + "declare @MyTMPDB int "
             + "select @Mycpu = COUNT(*) from sys.dm_os_schedulers where status = 'Visible online' "
             + "use tempdb "
             + "select @mytmpdb = COUNT(*) from sys.database_files where type = 0 "
             + "select @MyCPU,@MyTMPDB ";
            cmd0 = new SqlCommand(query_tempdb_cpu, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_tempdb_cpu;
            try
            {
                using (var dr_num_tempdb_cpu = cmd0.ExecuteReader())
                {
                    while (dr_num_tempdb_cpu.Read())
                    {
                        XmlElement num_tempdb_cpu = xmlDoc.CreateElement("NumOfTempDB");
                        num_tempdb_cpu.SetAttribute("cpu", dr_num_tempdb_cpu[0].ToString());
                        num_tempdb_cpu.SetAttribute("num", dr_num_tempdb_cpu[1].ToString());
                        systemMaintain.AppendChild(num_tempdb_cpu);
                    }
                    dr_num_tempdb_cpu.Close();
                }
             }
            catch (Exception ex)
            {
                Console.WriteLine("get numtempdb error");
            }

                //Check the number of Trace file  
                Console.WriteLine("    SQLTrace----");
                string query_sqltrace = "select COUNT(*) from sys.traces";
                cmd0 = new SqlCommand(query_sqltrace, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_sqltrace;
                try
                {
                    using (var dr_num_sqltrace = cmd0.ExecuteReader())
                    {
                        while (dr_num_sqltrace.Read())
                        {
                            XmlElement num_tsqltrace = xmlDoc.CreateElement("SQLTrace");
                            num_tsqltrace.InnerText = dr_num_sqltrace[0].ToString();
                            systemMaintain.AppendChild(num_tsqltrace);
                        }
                        dr_num_sqltrace.Close();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("get sqltrace error");
                    return;
                }
            /*

             * */
        }

        public void DBConfigCheck()//check the database configurations
        {
            Console.WriteLine("DBConfigCheck----");
            //--run the sp_configure
            XmlElement DBConfig = xmlDoc.CreateElement("DBConfig");
            mInstance.AppendChild(DBConfig);
            string query_dbconfig1 = "sp_configure 'show',1 \n"
            + "\nreconfigure \n";
            SqlCommand cmd0 = new SqlCommand(query_dbconfig1, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_dbconfig1;
            //Console.WriteLine(query_dbconfig);
            cmd0.ExecuteReader().Close();
            string query_dbconfig2 = "sp_configure";
            cmd0 = new SqlCommand(query_dbconfig2, mConnection);
            cmd0.CommandText = query_dbconfig2;
            var dr_dbconfig = cmd0.ExecuteReader();
            #region

            while (dr_dbconfig.Read())
            {
                switch (dr_dbconfig[0].ToString())
                {
                    case "affinity mask":
                        {
                            XmlElement AffinityMask = xmlDoc.CreateElement("AffinityMask");
                            AffinityMask.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(AffinityMask);
                        } break;
                    case "awe enabled":
                        {
                            XmlElement AweEnabled = xmlDoc.CreateElement("AWE");
                            AweEnabled.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(AweEnabled);
                        } break;
                    case "c2 audit mode":
                        {
                            XmlElement C2AuditMode = xmlDoc.CreateElement("C2Audit");
                            C2AuditMode.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(C2AuditMode);
                        } break;
                    case "clr enabled":
                        {
                            XmlElement CLREnabled = xmlDoc.CreateElement("CLR");
                            CLREnabled.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(CLREnabled);
                        } break;
                    case "cross db ownership chaining":
                        {
                            XmlElement CrossDBOwnershipChaining = xmlDoc.CreateElement("CrossDBOwnership");
                            CrossDBOwnershipChaining.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(CrossDBOwnershipChaining);
                        } break;
                    case "default trace enabled":
                        {
                            XmlElement DefaultTraceEnabled = xmlDoc.CreateElement("DefaultTrace");
                            DefaultTraceEnabled.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(DefaultTraceEnabled);
                        } break;
                    case "lightweight pooling":
                        {
                            XmlElement LightweightPooling = xmlDoc.CreateElement("LightweightPooling");
                            LightweightPooling.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(LightweightPooling);
                        } break;
                    case "locks":
                        {
                            XmlElement Locks = xmlDoc.CreateElement("Locks");
                            Locks.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(Locks);
                        } break;
                    case "max degree of parallelism":
                        {
                            XmlElement MaxDegreeOfParallelism = xmlDoc.CreateElement("MaxDOP");
                            MaxDegreeOfParallelism.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(MaxDegreeOfParallelism);
                        } break;
                    case "max server memory (MB)":
                        {
                            XmlElement MaxServerMemory = xmlDoc.CreateElement("MaxServerMemory");
                            MaxServerMemory.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(MaxServerMemory);
                        } break;
                    case "max worker threads":
                        {
                            XmlElement MaxWorkerThreads = xmlDoc.CreateElement("MaxWorkerThreads");
                            MaxWorkerThreads.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(MaxWorkerThreads);
                        } break;
                    case "network packet size (B)":
                        {
                            XmlElement NetworkPacketSize_B = xmlDoc.CreateElement("NetworkPacketSize");
                            NetworkPacketSize_B.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(NetworkPacketSize_B);
                        } break;
                    case "priority boost":
                        {
                            XmlElement PriorityBoost = xmlDoc.CreateElement("PriorityBoost");
                            PriorityBoost.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(PriorityBoost);
                        } break;
                    case "set working set size":
                        {
                            XmlElement SetWorkingSetSize = xmlDoc.CreateElement("SetWorkingsetSize");
                            SetWorkingSetSize.InnerText = dr_dbconfig[4].ToString();
                            DBConfig.AppendChild(SetWorkingSetSize);
                        } break;

                }
            }
            dr_dbconfig.Close();
            #endregion
            string query_physical_memory = "xp_msver 'PhysicalMemory'";
            cmd0 = new SqlCommand(query_physical_memory, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_physical_memory;
            var dr_physical_memory = cmd0.ExecuteReader();
            while (dr_physical_memory.Read())
            {
                XmlElement PhysicalMemory = xmlDoc.CreateElement("PhysicalMemory");
                PhysicalMemory.InnerText = dr_physical_memory[2].ToString();
                DBConfig.AppendChild(PhysicalMemory);
            }
            dr_physical_memory.Close();
        }

        //check logins and passwords
        public void LoginPWDCheck()
        {
            Console.WriteLine("LoginPWDCheck----");
            XmlElement LoginPWD = xmlDoc.CreateElement("LoginPWD");
            mInstance.AppendChild(LoginPWD);
            //Check Password Policy
            Console.WriteLine("    PasswordPolicy----");
            XmlElement PasswordPolicy = xmlDoc.CreateElement("PasswordPolicy");
            LoginPWD.AppendChild(PasswordPolicy);
            string query_pwd_policy = "select name from sys.sql_logins where is_policy_checked = 0";
            SqlCommand cmd0 = new SqlCommand(query_pwd_policy, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_pwd_policy;
            var dr_pwd_policy = cmd0.ExecuteReader();
            while (dr_pwd_policy.Read())
            {
                XmlElement LoginName = xmlDoc.CreateElement("LoginName");
                LoginName.InnerText = dr_pwd_policy[0].ToString();
                PasswordPolicy.AppendChild(LoginName);
            }
            dr_pwd_policy.Close();

            ///Check the admin permission:
            Console.WriteLine("    CheckAdminPermission----");
            XmlElement AdminPermission = xmlDoc.CreateElement("CheckAdminPermission");
            LoginPWD.AppendChild(AdminPermission);
            string query_admin_permission = "select name, IS_SRVROLEMEMBER('sysadmin',name) as IsAdmin from sys.server_principals where type = 'G'";
            cmd0 = new SqlCommand(query_admin_permission, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_admin_permission;
            try
            {
                using (var dr_admin_permission = cmd0.ExecuteReader())
                {
                    while (dr_admin_permission.Read())
                    {
                        XmlElement GroupPermission = xmlDoc.CreateElement("Group");
                        GroupPermission.SetAttribute("Name", dr_admin_permission[0].ToString());
                        GroupPermission.SetAttribute("IsAdmin", dr_admin_permission[1].ToString());
                        AdminPermission.AppendChild(GroupPermission);
                    }
                    dr_admin_permission.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("CheckAdminPermission error");
                return;
            }
            //Whether there is any user has blank password
            Console.WriteLine("    BlankPassword----");
            XmlElement BlankPWD = xmlDoc.CreateElement("BlankPassword");
            LoginPWD.AppendChild(BlankPWD);
            string query_blank_pwd = "select name from sys.syslogins where pwdcompare('', password) = 1";
            cmd0 = new SqlCommand(query_blank_pwd, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_blank_pwd;
            var dr_blank_password = cmd0.ExecuteReader();
            while (dr_blank_password.Read())
            {
                XmlElement BlankPasswordLoginName = xmlDoc.CreateElement("LoginName");
                BlankPasswordLoginName.InnerText = dr_blank_password[0].ToString();
                BlankPWD.AppendChild(BlankPasswordLoginName);
            }
            dr_blank_password.Close();

            //Whether there is any login password is the same as user name
            Console.WriteLine("    PwdSameAsUserName----");
            XmlElement PwdSameAsUserName = xmlDoc.CreateElement("PwdSameAsUserName");
            LoginPWD.AppendChild(PwdSameAsUserName);
            string query_pwd_sameas_username = "select name from sys.syslogins where pwdcompare(name, password) = 1";
            cmd0 = new SqlCommand(query_pwd_sameas_username, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_pwd_sameas_username;
            var dr_pwd_sameas_username = cmd0.ExecuteReader();
            while (dr_pwd_sameas_username.Read())
            {
                XmlElement SamePwdasUernameLoginName = xmlDoc.CreateElement("LgoinName");
                SamePwdasUernameLoginName.InnerText = dr_blank_password[0].ToString();
                PwdSameAsUserName.AppendChild(SamePwdasUernameLoginName);
            }
            dr_pwd_sameas_username.Close();

        }

        //Runtime check
        public void RuntimeCheck()
        {
            Console.WriteLine("RuntimeCheck----");
            XmlElement RuntimeCheck = xmlDoc.CreateElement("RuntimeCheck");
            mInstance.AppendChild(RuntimeCheck);
            //Check non-yielding issue
            Console.WriteLine("    Nonyielding----");
            int iresultCount = 0;
            SqlCommand cmd0 = new SqlCommand("sp_readerrorlog", mConnection);
            cmd0.CommandType = CommandType.StoredProcedure;
            cmd0.Parameters.Add(new SqlParameter("@p1", SqlDbType.Int, 4, "p1"));
            cmd0.Parameters.Add(new SqlParameter("@p2", SqlDbType.Int, 4, "p2"));
            cmd0.Parameters.Add(new SqlParameter("@p3", SqlDbType.VarChar, 255, "p3"));
            cmd0.Parameters.Add(new SqlParameter("@p4", SqlDbType.VarChar, 255, "p4"));
            cmd0.Parameters[1].Value = 1;
            cmd0.Parameters[2].Value = "";
            try
            {
                for (int j = 0; j < 2; j++)
                {
                    cmd0.Parameters[3].Value = j > 0 ? "似乎无法完成" : "non-yielding";
                    for (int i = 0; i < 5; i++)
                    {
                        cmd0.Parameters[0].Value = i + 1;
                        iresultCount += cmd0.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("There are less than 4 log files in your instance");
            }

            XmlElement WhetherNonYieldingIssues = xmlDoc.CreateElement("Nonyielding");
            WhetherNonYieldingIssues.InnerText = (iresultCount > 0 ? 1 : 0).ToString();
            RuntimeCheck.AppendChild(WhetherNonYieldingIssues);

            //check AV
            Console.WriteLine("    AV----");
            iresultCount = 0;
            cmd0.Parameters[3].Value = "Access Violate";
            try
            {
                for (int i = 0; i < 5; i++)
                {
                    cmd0.Parameters[0].Value = i + 1;
                    iresultCount += cmd0.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("There are less than 4 log files in your instance");
            }
            XmlElement WhetherAV = xmlDoc.CreateElement("AV");
            WhetherAV.InnerText = (iresultCount > 0 ? 1 : 0).ToString();
            RuntimeCheck.AppendChild(WhetherAV);

            //Check Virtual Log file issue:
            Console.WriteLine("    VirtualLogFile----");
            XmlElement VirtualLogFile = xmlDoc.CreateElement("VirtualLogFile");
            RuntimeCheck.AppendChild(VirtualLogFile);
            //create a sp CheckVLF to check the VLF
            iresultCount = 0;
            //use master
            string query_use_master = "use master";
            cmd0 = new SqlCommand(query_use_master, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_use_master;
            cmd0.ExecuteReader().Close();
            //
            string query_createSP_CheckVLF = "create proc CheckVLF "
            + "@myDBName nvarchar(2000) "
            + "as "
            + "CREATE TABLE #VLFs ( "
            + "FileId  INT NOT NULL, "
            + "FileSize BIGINT NOT NULL, "
            + "StartOffset BIGINT NOT NULL, "
            + "SeqNo INT NOT NULL, "
            + "Status INT NOT NULL, "
            + "Parity INT NOT NULL, "
            + "CreateLSN DECIMAL (25,0)) "
            + "INSERT #VLFs EXEC ('DBCC LOGINFO ('+@myDBName+') WITH NO_INFOMSGS') "
            + "SELECT FileId , Status , COUNT(*) AS VLF_Count_Per_File FROM #VLFs "
            + "GROUP BY FileId, Status "
            + "DROP TABLE #VLFs ";
            try
            {
                cmd0 = new SqlCommand(query_createSP_CheckVLF, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_createSP_CheckVLF;
                cmd0.ExecuteReader().Close();
                //list all the dataBases
                string query_list_databases = "select name from sys.databases";
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(query_list_databases, mConnection);
                sda.Fill(ds, "AllDatabases");
                DataTable dt = ds.Tables["AllDatabases"];
                foreach (DataRow dataRow in dt.Rows)
                {
                    //Console.WriteLine(dataRow["name"]);
                    string query_runSP = "CheckVLF " + " '[" + dataRow["name"] + "]' ";
                    SqlCommand cmd = new SqlCommand(query_runSP, mConnection);
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = query_runSP;
                    try
                    {
                        using (var dr_runSP = cmd.ExecuteReader())
                        {
                            while (dr_runSP.Read())
                            {
                                // if (dr_runSP.GetInt32(2) > 250)
                                iresultCount = +dr_runSP.GetInt32(2);
                            }
                            XmlElement virtuallogfile = xmlDoc.CreateElement("DB");
                            virtuallogfile.SetAttribute("Name", dataRow["name"].ToString());
                            virtuallogfile.SetAttribute("Num", iresultCount.ToString());
                            VirtualLogFile.AppendChild(virtuallogfile);
                            dr_runSP.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("read checkvlf error");
                        return;
                    }
                }
                /*XmlElement virtuallogfile = xmlDoc.CreateElement("VirtualLogFile");
                virtuallogfile.InnerText = iresultCount.ToString();
                RuntimeCheck.AppendChild(virtuallogfile);*/
                ///drop the sp
                string query_dropSP = "drop proc CheckVLF";
                cmd0 = new SqlCommand(query_dropSP, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_dropSP;
                cmd0.ExecuteReader().Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Run procedure CheckVLF failed!");
                return;
            }
            //whether Memory trimming issue
            Console.WriteLine("    MemoryTrimming----");
            iresultCount = 0;
            cmd0 = new SqlCommand("sp_readerrorlog", mConnection);
            cmd0.CommandType = CommandType.StoredProcedure;
            cmd0.Parameters.Add(new SqlParameter("@p1", SqlDbType.Int, 4, "p1"));
            cmd0.Parameters.Add(new SqlParameter("@p2", SqlDbType.Int, 4, "p2"));
            cmd0.Parameters.Add(new SqlParameter("@p3", SqlDbType.VarChar, 255, "p3"));
            cmd0.Parameters.Add(new SqlParameter("@p4", SqlDbType.VarChar, 255, "p4"));
            cmd0.Parameters[1].Value = 1;
            cmd0.Parameters[2].Value = "";
            try
            {
                for (int j = 0; j < 2; j++)
                {
                    cmd0.Parameters[3].Value = j > 0 ? "内存的一大部分已调出" : "A significant part of sql server process";
                    for (int i = 0; i < 5; i++)
                    {
                        cmd0.Parameters[0].Value = i + 1;
                        iresultCount += cmd0.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("There are less than 4 log files in your instance");
            }
            finally
            {
                DeleteTempProc("CheckVLF");
            }
            XmlElement WhetherMemorytrimmingIssues = xmlDoc.CreateElement("MemoryTrimming");
            WhetherMemorytrimmingIssues.InnerText = (iresultCount > 0 ? 1 : 0).ToString();
            RuntimeCheck.AppendChild(WhetherMemorytrimmingIssues);
            //IO bottleneck
            Console.WriteLine("    IOBottleneck----");
            iresultCount = 0;
            cmd0 = new SqlCommand("sp_readerrorlog", mConnection);
            cmd0.CommandType = CommandType.StoredProcedure;
            cmd0.Parameters.Add(new SqlParameter("@p1", SqlDbType.Int, 4, "p1"));
            cmd0.Parameters.Add(new SqlParameter("@p2", SqlDbType.Int, 4, "p2"));
            cmd0.Parameters.Add(new SqlParameter("@p3", SqlDbType.VarChar, 255, "p3"));
            cmd0.Parameters.Add(new SqlParameter("@p4", SqlDbType.VarChar, 255, "p4"));
            cmd0.Parameters[1].Value = 1;
            cmd0.Parameters[2].Value = "";
            try
            {
                for (int j = 0; j < 2; j++)
                {
                    cmd0.Parameters[3].Value = j > 0 ? "of I/O requests taking longer than" : "最新的长时间 I/O";
                    for (int i = 0; i < 5; i++)
                    {
                        cmd0.Parameters[0].Value = i + 1;
                        iresultCount += cmd0.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("There are less than 4 log files in your instance");
            }
            XmlElement WhetherIOBottleneck = xmlDoc.CreateElement("IOBottleneck");
            WhetherIOBottleneck.InnerText = (iresultCount > 0 ? 1 : 0).ToString();
            RuntimeCheck.AppendChild(WhetherIOBottleneck);
            //Check UserToken Cache issue
            Console.WriteLine("    UserTokenCache----");
            string query_user_token_cache = "select (single_pages_kb+multi_pages_kb)/1024 as size_MB from sys.dm_os_memory_clerks where type = 'USERSTORE_TOKENPERM' and name = 'TokenAndPermUserStore'";
            cmd0 = new SqlCommand(query_user_token_cache, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_user_token_cache;
            iresultCount = cmd0.ExecuteNonQuery();
            XmlElement UserTokenCache = xmlDoc.CreateElement("UserTokenCache");
            UserTokenCache.InnerText = (iresultCount > 0 ? 1 : 0).ToString();
            RuntimeCheck.AppendChild(UserTokenCache);



        }

        //DB option Check
        public void DBOptionCheck()
        {
            Console.WriteLine("DBOptions----");
            XmlElement DBOptions = xmlDoc.CreateElement("DBOptions");
            mInstance.AppendChild(DBOptions);
            //int iresultCount = 0;
            //Database Compatibility Level Check:
            Console.WriteLine("    CompatibilityLevel----");
            XmlElement DBCompatibility = xmlDoc.CreateElement("CompatibilityLevel");
            DBOptions.AppendChild(DBCompatibility);
            string query_db_compatibility_check = "select name,compatibility_level from sys.databases where compatibility_level not in(select compatibility_level from sys.databases where name ='master')";
            SqlCommand cmd0 = new SqlCommand(query_db_compatibility_check, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_db_compatibility_check;
            try
            {

                using (var dr_db_compatibility = cmd0.ExecuteReader())
                {
                    while (dr_db_compatibility.Read())
                    {
                        XmlElement DifLevelToSys = xmlDoc.CreateElement("DB");
                        DifLevelToSys.SetAttribute("Name", dr_db_compatibility[0].ToString());
                        DifLevelToSys.SetAttribute("CompatibilityLevel", dr_db_compatibility[1].ToString());
                        DBCompatibility.AppendChild(DifLevelToSys);
                    }
                    dr_db_compatibility.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("get CompatibilityLevel error");
                return;
            }
            //Whether the database has AutoClose feature enabled: 
            Console.WriteLine("    AutoClose----");
            XmlElement AutoClose = xmlDoc.CreateElement("AutoClose");
            DBOptions.AppendChild(AutoClose);
            string query_auto_close = "select name from sys.databases where is_auto_close_on = 1 ";
            cmd0 = new SqlCommand(query_auto_close, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_auto_close;
            var dr_auto_close = cmd0.ExecuteReader();
            while (dr_auto_close.Read())
            {
                XmlElement AutoCloseDB = xmlDoc.CreateElement("DB");
                AutoCloseDB.SetAttribute("Name", dr_auto_close[0].ToString());
                AutoClose.AppendChild(AutoCloseDB);
            }
            dr_auto_close.Close();

            //Whether the database has page verify option set to off. 
            Console.WriteLine("    PageVerify----");
            XmlElement VeryfySetOff = xmlDoc.CreateElement("PageVerify");
            DBOptions.AppendChild(VeryfySetOff);
            string query_veryfy_setoff = "select name from sys.databases where page_verify_option <> 2 ";
            cmd0 = new SqlCommand(query_veryfy_setoff, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_veryfy_setoff;
            var dr_veryfy_setoff = cmd0.ExecuteReader();
            while (dr_veryfy_setoff.Read())
            {
                XmlElement VeryfyOptionSetOff = xmlDoc.CreateElement("DB");
                VeryfyOptionSetOff.SetAttribute("Name", dr_veryfy_setoff[0].ToString());
                VeryfySetOff.AppendChild(VeryfyOptionSetOff);
            }
            dr_veryfy_setoff.Close();

            //Whether there is any file whose growth is set to by percentage. 
            Console.WriteLine("    GrowByPercentage----");
            XmlElement GrowByPercentage = xmlDoc.CreateElement("GrowByPercentage");
            DBOptions.AppendChild(GrowByPercentage);
            string query_grow_by_percentage = "select name from sys.master_files where is_percent_growth = 1 ";
            cmd0 = new SqlCommand(query_grow_by_percentage, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_grow_by_percentage;
            var dr_grow_by_percentage = cmd0.ExecuteReader();
            while (dr_grow_by_percentage.Read())
            {
                XmlElement GrowByPercentageRow = xmlDoc.CreateElement("DB");
                GrowByPercentageRow.SetAttribute("Name", dr_grow_by_percentage[0].ToString());
                GrowByPercentage.AppendChild(GrowByPercentageRow);
            }
            dr_grow_by_percentage.Close();
        }

        //Perfern Check
        public void PerfCheck()
        {
            Console.WriteLine("PerfCheck----");
            XmlElement PerfCheck = xmlDoc.CreateElement("PerfCheck");
            mInstance.AppendChild(PerfCheck);
            ulong iresultCount = 0;
            //Check the SQL Server Memory Usage (May run this script several times in a loop)
            Console.WriteLine("    PageLifeExpectancy----");
            string query_memory_usage = "Select * from sys.dm_os_performance_counters where counter_name = 'Page life expectancy' and object_name = 'SQLServer:Buffer Manager'";
            SqlCommand cmd0 = new SqlCommand(query_memory_usage, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_memory_usage;
            long iMemoryUsage = 0;
            for (int i = 0; i < 5; i++)
            {
                var dr_memory_usage = cmd0.ExecuteReader();
                while (dr_memory_usage.Read())
                {
                    iMemoryUsage += dr_memory_usage.GetInt64(3);
                    Thread.Sleep(10);
                }
                dr_memory_usage.Close();
            }
            XmlElement MemoryUsage = xmlDoc.CreateElement("PageLifeExpectancy");
            MemoryUsage.InnerText = (iMemoryUsage / 5).ToString();
            PerfCheck.AppendChild(MemoryUsage);
            //Check whether the system has blocking (May run this script several times in a loop)
            Console.WriteLine("    Blocking----");
            string query_blocking = "select COUNT(*) from sys.dm_exec_requests where blocking_session_id <> 0 ";
            cmd0 = new SqlCommand(query_blocking, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_blocking;
            int iBlockingSessions = 0;
            for (int i = 0; i < 5; i++)
            {
                var dr_blocking = cmd0.ExecuteReader();
                while (dr_blocking.Read())
                {
                    iBlockingSessions += dr_blocking.GetInt32(0);
                    Thread.Sleep(10);
                }
                dr_blocking.Close();
            }
            XmlElement BlockingNum = xmlDoc.CreateElement("Blocking");
            BlockingNum.InnerText = (iBlockingSessions / 5).ToString();
            PerfCheck.AppendChild(BlockingNum);


            //Check the SQL Server CPU usage (May run this several times in a loop)
            Console.WriteLine("    CPU_UT----");
            string query_sql_cpu_usage = "SELECT Name,PercentProcessorTime FROM Win32_PerfFormattedData_PerfProc_Process where Name='sqlservr'";
            SelectQuery selectQuery = new SelectQuery(query_sql_cpu_usage);
            ManagementObjectSearcher searcher;
            for (int i = 0; i < 5; i++)
            {
                searcher = new ManagementObjectSearcher(selectQuery);
                foreach (ManagementObject mo in searcher.Get())
                {
                    //Console.WriteLine(Convert.ToUInt64(mo["PercentProcessorTime"]));
                    iresultCount += Convert.ToUInt64(mo["PercentProcessorTime"]);
                }
                Thread.Sleep(10);
            }
            XmlElement SQLServerCpuUsage = xmlDoc.CreateElement("CPU_UT");
            SQLServerCpuUsage.InnerText = (iresultCount / 5).ToString();
            PerfCheck.AppendChild(SQLServerCpuUsage);

            //check the Disk Perfermance
            Console.WriteLine("    DiskPerformance----");
            XmlElement DiskPerf = xmlDoc.CreateElement("DiskPerformance");
            PerfCheck.AppendChild(DiskPerf);
            string query_disk_perm = "SELECT DiskTransfersPersec FROM Win32_PerfFormattedData_PerfDisk_LogicalDisk ";
            UInt32 iDiskSecTransfer = 0;
            for (int i = 0; i < 3; i++)
            {
                iDiskSecTransfer = 0;
                try
                {
                    selectQuery = new SelectQuery(query_disk_perm);
                    searcher = new ManagementObjectSearcher(selectQuery);
                    foreach (ManagementObject mo in searcher.Get())
                    {
                        iDiskSecTransfer = +Convert.ToUInt32(mo["DiskTransfersPersec"]);
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("wmi call failed!");
                    return;
                }
                XmlElement DiskPerfRow = xmlDoc.CreateElement("DiskSecTransfer");
                DiskPerfRow.SetAttribute("Time", DateTime.Now.ToString());
                DiskPerfRow.SetAttribute("DiskSec", iDiskSecTransfer.ToString());
                DiskPerf.AppendChild(DiskPerfRow);
                Thread.Sleep(1000);
            }
            //SQL Server Deadlock check
            Console.WriteLine("    DeadlockCheck----");
            string query_deadlocknum = "select * from sys.dm_os_performance_counters  where counter_name like '%deadlock%' and instance_name = '_Total'";
            cmd0 = new SqlCommand(query_deadlocknum, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_deadlocknum;
            var dr_deadlocksnum = cmd0.ExecuteReader();
            while (dr_deadlocksnum.Read())
            {
                XmlElement DeadLockRow = xmlDoc.CreateElement("DeadlockCheck");
                DeadLockRow.InnerText = dr_deadlocksnum[3].ToString();
                PerfCheck.AppendChild(DeadLockRow);
            }
            dr_deadlocksnum.Close();

            //Get overall performance data by

        }

        //Index Check
        public void IndexCheck()
        {

            Console.WriteLine("    IndexCheck----");
            string query_use_master = "use master";
            SqlCommand cmd0 = new SqlCommand(query_use_master, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_use_master;
            cmd0.ExecuteReader().Close();
            XmlElement IndexCheck = xmlDoc.CreateElement("IndexCheck");
            mInstance.AppendChild(IndexCheck);
            //Check index fragmentation
            Console.WriteLine("    IndexFrag----");
            XmlElement IndexFrag = xmlDoc.CreateElement("IndexFrag");
            IndexCheck.AppendChild(IndexFrag);
                string query_need_defrag = "SELECT d.name as DatabaseName, b.name as TableName, c.name as IndexName FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) a join sys.objects b on a.object_id = b.object_id  join sys.indexes c on a.index_id = c.index_id and c.object_id = a.object_id join sys.databases d on a.database_id =d.database_id where   d.state_desc = 'ONLINE' and avg_fragmentation_in_percent >30.00  ";
                cmd0 = new SqlCommand(query_need_defrag, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_need_defrag;
                using (var dr_need_defrag = cmd0.ExecuteReader())
                {

                    try
                    {
                        while (dr_need_defrag.Read())
                        {
                            XmlElement NeedIndexDefrag = xmlDoc.CreateElement("Index");
                            NeedIndexDefrag.SetAttribute("DBName", dr_need_defrag[0].ToString());
                            NeedIndexDefrag.SetAttribute("TableName", dr_need_defrag[1].ToString());
                            NeedIndexDefrag.SetAttribute("IndexName", dr_need_defrag[2].ToString());
                            IndexFrag.AppendChild(NeedIndexDefrag);
                        }
                        dr_need_defrag.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("some database may not online");
                        return;
                    }

                }
            //Check Statistics Update info:
            Console.WriteLine("    StatisticsUpdateInfo----");
            XmlElement StatisticsUpdateInfo = xmlDoc.CreateElement("StatisticsUpdateInfo");
            IndexCheck.AppendChild(StatisticsUpdateInfo);

            string query_create_sp = "create proc CheckStats "
             + "@myDBName nvarchar(2000) "
             + "as "
             + "Declare @myStr nvarchar(4000) "
             + "set @myStr = 'SELECT object_name(object_id) as tableName,a.name AS stats_name, "
             + "STATS_DATE(object_id, stats_id) AS statistics_update_date "
             + "FROM ' +@mydbname+ '.sys.stats a join sysobjects b on a.object_id = b.id where DATEDIFF(DAYOFYEAR,STATS_DATE(object_id, stats_id),GETDATE())>30 and xtype =''U''' "
             + "if @myDBName not in (select '['+name + ']' from sys.databases where database_id <5) "
             + "begin "
             + "exec(@mystr) "
             + "end ";
            cmd0 = new SqlCommand(query_create_sp, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_create_sp;
            cmd0.ExecuteReader().Close();
            try
            {
                //list all the dataBase
                string query_list_databases = "select name from sys.databases";
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(query_list_databases, mConnection);
                sda.Fill(ds, "AllDatabases");
                DataTable dt = ds.Tables["AllDatabases"];
                foreach (DataRow dataRow in dt.Rows)
                {
                    //Console.WriteLine(dataRow["name"]);
                    string query_runSP = "CheckStats " + " '[" + dataRow["name"] + "]' ";
                    SqlCommand cmd = new SqlCommand(query_runSP, mConnection);
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = query_runSP;
                    var dr_runSP = cmd.ExecuteReader();
                    while (dr_runSP.Read())
                    {
                        XmlElement NoUpdate30Days = xmlDoc.CreateElement("Statistics");
                        NoUpdate30Days.SetAttribute("DBName", dataRow["name"].ToString());
                        NoUpdate30Days.SetAttribute("TableName", dr_runSP[0].ToString());
                        NoUpdate30Days.SetAttribute("StatisticsUpdateDate", dr_runSP.GetSqlDateTime(2).ToString());
                        NoUpdate30Days.SetAttribute("StatsName", dr_runSP[1].ToString());
                        StatisticsUpdateInfo.AppendChild(NoUpdate30Days);
                    }
                    dr_runSP.Close();
                }
                ///drop the sp
                string query_dropSP = "drop proc CheckStats";
                cmd0 = new SqlCommand(query_dropSP, mConnection);
                cmd0.CommandType = CommandType.Text;
                cmd0.CommandText = query_dropSP;
                cmd0.ExecuteReader().Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("run procedure CheckStats failed!");
                return;
            }
            finally
            {
                DeleteTempProc("CheckStats");
            }
            //Missing Index
            Console.WriteLine("    MissingIndex----");
            XmlElement MissingIndex = xmlDoc.CreateElement("MissingIndex");
            IndexCheck.AppendChild(MissingIndex);
            string query_missing_index = "SELECT "
            + "migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, "
            + "'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) "
            + "+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' "
            + "+ ' ON ' + mid.statement "
            + "+ ' (' + ISNULL (mid.equality_columns,'') "
            + "+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END "
            + "+ ISNULL (mid.inequality_columns, '') "
            + "+ ')' "
            + "+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, "
            + "migs.*, mid.database_id, mid.[object_id] "
            + "FROM sys.dm_db_missing_index_groups mig "
            + "INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle "
            + "INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle "
            + "WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 "
            + "ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC";
            cmd0 = new SqlCommand(query_missing_index, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_missing_index;
            try
            {
                using (var dr_missing_index = cmd0.ExecuteReader())
                {
                    while (dr_missing_index.Read())
                    {
                        XmlElement MissingIndexRow = xmlDoc.CreateElement("Index");
                        MissingIndexRow.SetAttribute("DBID", dr_missing_index[16].ToString());
                        MissingIndexRow.SetAttribute("CreateIndexStatement", dr_missing_index[1].ToString());
                        MissingIndexRow.SetAttribute("ImprovementMeasure", dr_missing_index[0].ToString());
                        MissingIndex.AppendChild(MissingIndexRow);
                    }
                    dr_missing_index.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("missing index error");
                return;
            }
        }

        public void SaveXmlFile()
        {
            try
            {
                xmlDoc.Save(mXmlPath);
                Console.WriteLine("SaveXmlFile----");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Save XML failed! Please check the file path is correct of you have the permission.");
                System.Environment.Exit(0);
            }
        }

        public void DeleteTempProc(string procname)//to ensure safety 
        {
            string query_drop_temp_proc = "if exists (select * from sysobjects where id = object_id('" + procname + "') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure " + procname;
            SqlCommand cmd0 = new SqlCommand(query_drop_temp_proc, mConnection);
            cmd0.CommandType = CommandType.Text;
            cmd0.CommandText = query_drop_temp_proc;
            cmd0.ExecuteReader().Close();
        }

        //main 
        static void Main(string[] args)
        {
            //Console.WriteLine("xxx");
            //Debug.WriteLine("xx");
            string csServerName,csInstanceName,csXmlPath;
            XMLWriter xmlWriter;
            //XMLWriter xmlWriter = new XMLWriter("GUOJING-MSFT", "MSSQLSERVER");
            /*Console.Write("Please Input\nServer Name:");
            csServerName = Console.ReadLine();
            Console.Write("Instance Name:");
            csInstanceName = Console.ReadLine();
            XMLWriter xmlWriter = new XMLWriter(csServerName, csInstanceName);
             * */
            
            //the following block was commented out on 2011-11-10 by jiayiw

            //if (args.Length==2)
            //{
            //    csServerName = args[0];
            //    csInstanceName = args[1];
            //    xmlWriter= new XMLWriter(csServerName, csInstanceName);
            //}
            //else if (args.Length == 3)
            //{
            //    csServerName = args[0];
            //    csInstanceName = args[1];
            //    csXmlPath = args[2];
            //    xmlWriter = new XMLWriter(csServerName, csInstanceName,csXmlPath);
            //}
            //else
            //{
            //    Console.WriteLine("arguments error!");
            //    System.Environment.Exit(0);
            //    xmlWriter = new XMLWriter("GUOJING-MSFT", "MSSQLSERVER");
            //}

            if (args.Length == 2)
            {
                int i = 0;
                i = args[0].IndexOf('\\');
                Console.WriteLine(i);
                if (i!=-1)
                {
                    csInstanceName = args[0].Substring(i + 1);
                    csServerName = args[0].Substring(0, i);
                    Console.WriteLine(csServerName);
                    Console.WriteLine(csInstanceName);
                    csXmlPath = args[1];
                    xmlWriter = new XMLWriter(csServerName, csInstanceName, csXmlPath);
                }
                else
                {
                    csServerName = args[0];
                    csInstanceName = "MSSQLSERVER";
                    csXmlPath = args[1];
                    xmlWriter = new XMLWriter(csServerName, csInstanceName, csXmlPath);
                }

            }
            else 
            {
                Console.WriteLine("arguments error!");
                System.Environment.Exit(0);
                csXmlPath = args[1];
                xmlWriter = new XMLWriter("GUOJING-MSFT", "MSSQLSERVER");
            }

            Console.WriteLine(csXmlPath);
            xmlWriter.SystemCheck();
            xmlWriter.SystemMaintainCheck();
            xmlWriter.DBConfigCheck();
            xmlWriter.LoginPWDCheck();
            xmlWriter.RuntimeCheck();
            xmlWriter.DBOptionCheck();
            xmlWriter.PerfCheck();
            xmlWriter.IndexCheck();
            xmlWriter.SaveXmlFile();
            xmlWriter.Close();
        }

    }
}
